package com.dp.service.impl3;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.SnAreaVo;
import com.dp.util.DataMap;
import com.dp.vo.NewsGroup;
import com.dp.vo.NewsK16control;
import com.dp.vo.NewsSnArea;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class K16controlServiceImpl extends BaseImpl {
	/**
	 * Find all the findAllK16controlEntity Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<NewsK16control>
	 */
	public List<NewsK16control> findAllK16controlEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsK16control k16control=(NewsK16control) hashMap.get("k16control");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsK16control n WHERE 1=1 ");
		if(k16control!=null){
			if(k16control.getSn()!=null&& k16control.getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.sn like '%"
						+ k16control.getSn().toString().trim() + "%'");
			}
			if(k16control.getSntype()!=null&& k16control.getSntype().toString().trim().length() > 0){
				sqlS.append(" AND n.sntype like '%"
						+ k16control.getSntype().toString().trim() + "%'");
			}
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		List<NewsK16control> queryList=new ArrayList<NewsK16control>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	/**
	 * k16 升级控制sn 记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllK16controlEntityCount(DataMap hashMap){
		NewsK16control k16control=(NewsK16control) hashMap.get("k16control");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.sn) FROM NewsK16control n WHERE 1=1 ");
		if(k16control!=null){
			if(k16control.getSn()!=null&& k16control.getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.sn like '%"
						+ k16control.getSn().toString().trim() + "%'");
			}
			if(k16control.getSntype()!=null&& k16control.getSntype().toString().trim().length() > 0){
				sqlS.append(" AND n.sntype='"
						+ k16control.getSntype().toString().trim() + "'");
			}
		}
		
		Query query=null;
		try {
			query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * SN与区域对应信息总记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllSnAreaEntityCount(DataMap hashMap){
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		Integer iCount = 0; 
		StringBuffer sqlS = new StringBuffer("SELECT count(n.newsSnAreaPK.sn) FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		try {
			Query query = em.createQuery(sqlS.toString());
			iCount = Integer.valueOf(query.getSingleResult().toString());
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return iCount;
	}
	
	/**
	 * Find all the NewsSnArea Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<SnAreaVo>
	 */
	public List<SnAreaVo> findAllSnAreaEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		NewsSnArea snarea=(NewsSnArea) hashMap.get("snarea");
		NewsGroup group=(NewsGroup) hashMap.get("group");
		String areaids=(String) hashMap.get("areaids");
		String selectedSet=(String) hashMap.get("selectedSet");
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsSnArea n WHERE 1=1 ");
		if(snarea!=null){
			if(snarea.getNewsSnAreaPK().getSn()!=null&& snarea.getNewsSnAreaPK().getSn().toString().trim().length() > 0){
				sqlS.append(" AND n.newsSnAreaPK.sn like '%"
						+ snarea.getNewsSnAreaPK().getSn().toString().trim() + "%'");
			}
		}
		if(areaids!=null){
			sqlS.append(" AND n.newsSnAreaPK.areainfo.areaid in ("
					+ areaids + ")");
		}
		
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by n."+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		
		List<SnAreaVo> list=new ArrayList<SnAreaVo>();
		try {
			Query query = em.createQuery(sqlS.toString());
			if (firstrow != null) {
				query.setFirstResult(firstrow);
			}
			if (maxrow != null) {
				query.setMaxResults(maxrow);
			}
			List<NewsSnArea> queryList=new ArrayList<NewsSnArea>();
			queryList = query.getResultList();
			SnAreaVo snAreaVo = null;
			for(NewsSnArea snarea_temp:queryList){
				snAreaVo=new SnAreaVo();
				snAreaVo.setSn(snarea_temp.getNewsSnAreaPK().getSn());
				snAreaVo.setAreaid(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaid()+"");
				snAreaVo.setAreaname(snarea_temp.getNewsSnAreaPK().getAreainfo().getAreaname());
				list.add(snAreaVo);
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return list;
	}
	
	public Boolean addk16control(NewsK16control k16control) {
		Boolean resultb=true;
			String jpql="delete FROM NewsK16control n WHERE n.sn=:sn AND (n.sntype=1 or n.sntype=2) ";
			Query query = em.createQuery(jpql);
			query.setParameter("sn",k16control.getSn());
			query.executeUpdate();
			em.persist(k16control);
		
		return resultb;
	}
	
	public Boolean updatek16control(NewsK16control k16control,NewsK16control k16controlO) {
		Boolean resultb=true;
		try {
			String jpql="SELECT n FROM NewsK16control n WHERE n.sn=:sn AND n.sntype=:sntype";
			Query query = em.createQuery(jpql);
			query.setParameter("sn",k16control.getSn());
			query.setParameter("sntype", k16control.getSntype());
			NewsK16control k16control_temp= (NewsK16control) getSingleResult(query);
			boolean updateD=false;
			if(k16control_temp!=null){
				if(k16control_temp.getSn().equals(k16controlO.getSn())&&k16control_temp.getSntype().equals(k16controlO.getSntype())){
					updateD=true;
				}else{
					resultb=null;
				}
			}else{
				updateD=true;
			}
			if(updateD){
				jpql="UPDATE NewsK16control n SET n.sn=:sn,n.sntype=:sntype WHERE n.sn=:oldsn AND n.sntype=:oldsntype";
				query = em.createQuery(jpql);
				query.setParameter("sn",k16control.getSn());
				query.setParameter("sntype", k16control.getSntype());
				query.setParameter("oldsn", k16controlO.getSn());
				query.setParameter("oldsntype", k16controlO.getSntype());
				query.executeUpdate();
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	public boolean delk16control(List<NewsK16control> snvList) {
		boolean resultb=true;
//		try {
//			this.init();
//			this.sql="delete from news_k16control where sn=? and sntype=?";
//			prep=conn.prepareStatement(sql);
//			for(NewsK16control k16control:snvList){
//				prep.setString(1, k16control.getSn());
//				prep.setString(2, k16control.getSntype());
//				prep.addBatch();
//			}
//			prep.executeBatch();
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.error("deletek16controlSn fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	public Boolean addk16controlL(DataMap hashMap) {
		int sntype=Integer.parseInt(hashMap.get("sntype").toString()) ;
		List<SnAreaVo> salist= (List<SnAreaVo>)hashMap.get("salist");
		String strIdList = (String) hashMap.get("strIdList");
		Boolean resultb=true;
//		String [] strIdListarys=null;
//		try {
//			this.init();
//			if(salist!=null){
//				//全部
//				strIdListarys=new String[salist.size()];
//				for(int i=0;i<salist.size();i++){
//					strIdListarys[i]=salist.get(i).getSn()+"";
//				}
//			}else if(strIdList!=null&&!"".equals(strIdList)){
//				//加入,一个或多个
//				strIdListarys= strIdList.split(",");
//			}else{
//				return true;
//			}
//			this.sql="insert into news_k16control (sn, sntype) values (?, ?)";
//			prep=conn.prepareStatement(sql);
//			this.sql="delete from news_k16control where sn=? and (sntype=1 or sntype=2)";
//			prep2=conn.prepareStatement(sql);
//			for (String sn : strIdListarys) {
//				prep2.setString(1, sn);
//				prep2.addBatch();
//				prep.setString(1, sn);
//				prep.setInt(2, sntype);
//				prep.addBatch();
//			}
//			prep2.executeBatch();
//			prep.executeBatch();
//			conn.commit(); 
//		} catch (Exception e) {
//			logger.info("addk16controlSn fail !!!" + e.getMessage());
//			e.printStackTrace();
//			resultb=false;
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally {
//			this.closeAll();
//		}
		return resultb;
	}
	
	/**
	 * 预处理得到检索条件中所有的areaid
	 * @param area_id
	 * @return
	 */
	public String findAllAreaids(Integer area_id) {
		String areaids=null;
    	try {
	    	StringBuffer sqlS = new StringBuffer("SELECT p FROM TAreainfo p");
	        Query query = em.createQuery(sqlS.toString());  
	        List<TAreainfo> queryList=new ArrayList<TAreainfo>();
	        queryList=query.getResultList();  
	        if(queryList.size()>0){
	        	List<Integer> areaid_templist=new ArrayList<Integer>();
	        	areaid_templist = getAreaid_templist(queryList,area_id,areaid_templist);
	        	areaids="";
	        	for(Integer areaid_temp:areaid_templist){
	        		areaids+="'"+areaid_temp+"',";
	        	}
	        	if(!"".equals(areaids)){
	        		areaids=areaids.substring(0, areaids.length()-1);
	        	}
	        }
	        
    	} catch (Exception e) {
			e.printStackTrace();
		}
		return areaids;
	}
	
	private List<Integer> getAreaid_templist(List<TAreainfo> tailist,int areaid_temp,List<Integer> areaid_templist){
		areaid_templist.add(areaid_temp);
		for(TAreainfo tc3:tailist){
			if(tc3.getUpareaid().equals(areaid_temp)){
				if(tc3.getAreaid()!=0){
					getAreaid_templist(tailist,tc3.getAreaid(),areaid_templist);
				}
			}
		}
		return areaid_templist;
	}
	
	public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}
}
